using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_CARGO : DALConnection
    {
        #region Incluir

        public void incluir(T_CARGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_CARGO ");
                SQL.Append("    (TX_DSC_CARGO,FL_CARGO) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ('" + obj.TX_DSC_CARGO + "'," + obj.FL_CARGO.ToString() + ") ");

                DbCommand insertT_CARGO = database.GetSqlStringCommand(SQL.ToString());

                insertT_CARGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_CARGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_CARGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_CARGO SET ");
                SQL.Append("    TX_DSC_CARGO = '" + obj.TX_DSC_CARGO + "', ");
                SQL.Append("    FL_CARGO = " + obj.FL_CARGO.ToString() + " ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_CARGO = " + obj.ID_CARGO.ToString());

                DbCommand updateT_CARGO = database.GetSqlStringCommand(SQL.ToString());

                updateT_CARGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_CARGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_CARGO obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_CARGO ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_CARGO = " + obj.ID_CARGO.ToString());

                DbCommand deleteT_CARGO = database.GetSqlStringCommand(SQL.ToString());

                deleteT_CARGO.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_CARGO, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_CARGO obterPorPK(int pID_CARGO)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_CARGO ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_CARGO = " + pID_CARGO.ToString() + " ORDER BY TX_DSC_CARGO");

                DbCommand consultaT_CARGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_CARGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_CARGO, DALConnection.transaction);
                T_CARGO obj = null;

                if (reader.Read())
                {
                    obj = new T_CARGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CARGO")))
                    {
                        obj.ID_CARGO = Int32.Parse(reader["ID_CARGO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DSC_CARGO")))
                    {
                        obj.TX_DSC_CARGO = reader["TX_DSC_CARGO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_CARGO")))
                    {
                        obj.FL_CARGO = Int32.Parse(reader["FL_CARGO"].ToString());
                    }
                }
                reader.Dispose();
                consultaT_CARGO.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Listar

        public List<T_CARGO> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_CARGO ORDER BY FL_CARGO");

                DbCommand consultaT_CARGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_CARGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_CARGO, DALConnection.transaction);
                List<T_CARGO> lista = new List<T_CARGO>();
                T_CARGO obj = null;

                while (reader.Read())
                {
                    obj = new T_CARGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CARGO")))
                    {
                        obj.ID_CARGO = Int32.Parse(reader["ID_CARGO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DSC_CARGO")))
                    {
                        obj.TX_DSC_CARGO = reader["TX_DSC_CARGO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_CARGO")))
                    {
                        obj.FL_CARGO = Int32.Parse(reader["FL_CARGO"].ToString());
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_CARGO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<T_CARGO> consultar(string descricao)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_CARGO");
                SQL.Append(" WHERE TX_DSC_CARGO LIKE '" + descricao + "%' ORDER BY FL_CARGO");

                DbCommand consultaT_CARGO = database.GetSqlStringCommand(SQL.ToString());
                consultaT_CARGO.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_CARGO, DALConnection.transaction);
                List<T_CARGO> lista = new List<T_CARGO>();
                T_CARGO obj = null;

                while (reader.Read())
                {
                    obj = new T_CARGO();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CARGO")))
                    {
                        obj.ID_CARGO = Int32.Parse(reader["ID_CARGO"].ToString());
                    }

                    if (!reader.IsDBNull(reader.GetOrdinal("TX_DSC_CARGO")))
                    {
                        obj.TX_DSC_CARGO = reader["TX_DSC_CARGO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_CARGO")))
                    {
                        obj.FL_CARGO = Int32.Parse(reader["FL_CARGO"].ToString());
                    }
                    lista.Add(obj);

                }
                reader.Dispose();
                consultaT_CARGO.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
